package com.esm.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.esm.dao.SelectSalaryDao_zxk;
import com.esm.utils.JDBCUtil;

/**
 * 查询薪水dao实现
 * @author 康少
 *
 * 2017年1月10日
 */
public class SelectSalaryDaoImpl_zxk implements SelectSalaryDao_zxk{
	/**
	 * 查询当前用户的基本薪资Dao实现
	 */
	@Override
	public float getBasicSalary(String uname) {
		// TODO Auto-generated method stub
		float salary = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ems_salary.bsal");
		sql.append(" FROM ems_salary");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_salary.sal_id = ems_user.sal_id");
		sql.append(" WHERE ems_user.uname = ?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement  pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				salary = rs.getFloat("bsal");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return salary;
	}

	@Override
	public float getRealSalary(String uname, int year, int month) {
		// TODO Auto-generated method stub
		float overtimeNum = getOvertimeNum(uname,year,month);
		float btravelNum = getBtravelNum(uname, year, month);
		float leavelNum = getLeavelNum(uname, year, month);
		float realsalary = (overtimeNum*10)+(btravelNum*100)-(leavelNum*20);
		return realsalary;
	}
	/**
	 * 按月查询某人的加班数量
	 * @param uname
	 * @param year
	 * @param month
	 * @return
	 */
	public static float getOvertimeNum(String uname, int year, int month) {
		// TODO Auto-generated method stub
		float realsalary = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(time) jb");
		sql.append(" FROM ems_attend");
		sql.append(" WHERE ems_attend.uname = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND state_zxk = '加班'");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				realsalary = rs.getInt("jb");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return realsalary;
	}
	/**
	 * 按月查询某人的出差数量
	 * @param uname
	 * @param year
	 * @param month
	 * @return
	 */
	public static float getBtravelNum(String uname, int year, int month) {
		// TODO Auto-generated method stub
		float realsalary = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(time) cc");
		sql.append(" FROM ems_attend");
		sql.append(" WHERE ems_attend.uname = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND state_zxk = '出差'");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				realsalary = rs.getInt("cc");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return realsalary;
	}
	/**
	 * 按月查询某人的请假数量
	 * @param uname
	 * @param year
	 * @param month
	 * @return
	 */
	public static float getLeavelNum(String uname, int year, int month) {
		// TODO Auto-generated method stub
		float realsalary = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(time) qj");
		sql.append(" FROM ems_attend");
		sql.append(" WHERE ems_attend.uname = ?");
		sql.append(" AND YEAR(date) = ?");
		sql.append(" AND MONTH(date) = ?");
		sql.append(" AND state_zxk = '请假'");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setInt(2, year);
			pst.setInt(3, month);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				realsalary = rs.getInt("qj");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return realsalary;
	}
}
